import shortuuid
from sqlalchemy import and_, or_
from sqlalchemy.orm import joinedload, joinedload_all

from database import db
from ..models.resource import Resource
from ..models.category import ProductCategory
from ..models.metadata import Metadata
from ..models.product import Product, ProductContent, ProductMetadata, ProductGallery


def _process_value_metadata(query, metadata_def, value):
    '''
        value 为 str 的情况,
        muti_value 需要做like
        其他直接 等于
    '''
    conditions = []
    if metadata_def.is_muti_select():
        query = query.filter(and_(ProductMetadata.metadata_id == metadata_def.id,
                                  ProductMetadata.value.like('%{}%'.format(value))))
    else:
        query = query.filter(and_(ProductMetadata.metadata_id == metadata_def.id,
                                  ProductMetadata.value == value))
    return query


def _check_dice_metadata_value(value):
    if 'value' in value: return True
    if 'like' in value and not 'value' in value: return False
    if 'start' in value: return True
    if 'end' in value: return True

def _process_dict_metadata_value(query, metadata_def, value):
    conditions = []
    '''
        值为dict 的情况, 可处理
        {
            like and value  -> like value ,
            start  -> >=  start
            end    -> <=  end
            value  => = value
        }
    '''
    if 'value' in value:
        v = value.pop('value')
        like = value.pop('like', None)
        if like:
            query = query.filter(
                and_(ProductMetadata.metadata_id == metadata_def.id, ProductMetadata.value.like(value)))
        else:
            query = query.filter(and_(ProductMetadata.metadata_id == metadata_def.id, ProductMetadata.value == v))
    else:
        start = value.pop('start', None)
        end = value.pop('end', None)

        if start:
            query = query.filter(and_(ProductMetadata.metadata_id == metadata_def.id, ProductMetadata.value >= start))
        if end:
            query = query.filter(and_(ProductMetadata.metadata_id == metadata_def.id, ProductMetadata.value <= end))
    return query


def _process_list_metadata_value(query, metadata_def, value):
    if metadata_def.is_muti_select():
        ### 如果元数据定义的是 muti_select , 那么 product_metadata 的value 应该是以逗号分隔的
        ### 比如  a,b,c
        conditions = []
        for v in value:
            conditions.append(and_(ProductMetadata.metadata_id == metadata_def.id,
                                   ProductMetadata.value.like('%{}%'.format(v))))
        query = query.filter(or_(*conditions))
    elif metadata_def.is_select() or metadata_def.is_text():
        # 如果元数据定义的是 select 或 text,那么 product_metadata 的 value 应该是存的值
        conditions = []
        for v in value:
            conditions.append(and_(ProductMetadata.metadata_id == metadata_def.id,
                                   ProductMetadata.value == v))
        query = query.filter(or_(*conditions))

    return query


class ProductService(object):
    @classmethod
    def save(cls, product):
        if not product.id:
            product.id = shortuuid.uuid()
        db.session.add(product)
        db.session.commit()
        return product

    @classmethod
    def get(cls, **kwargs):
        query = Product.query;
        name = kwargs.pop('name')
        # language = kwargs.pop('language', None)
        # language = 'zh_CN'
        if name:
            query = query.filter(Product.title == name)
        # if language:
        #     query = query.join(ProductContent).filter(
        #         ProductContent.language_code == language)
        query = query.options(joinedload(Product.cover), joinedload(Product.category), joinedload(Product.extData),
                              joinedload(Product.contents))

        # print(len(query.all()))

        data = query.first()
        return query.first()

    @classmethod
    def get_by_id(cls, id):
        return Product.query.filter(Product.id == id).first()

    @classmethod
    def get_same_brand_product_list(cls, product, limit=None, include_self=False, include_removed=False):
        if not product: return []
        query = Product.query.filter(Product.brand_id == product.brand_id)

        if not include_self:
            query = query.filter(Product.id != product.id)
        if not include_removed:
            query = query.filter(Product.status != Product.STATUE_REMOVED)
        if limit:
            query = query.limit(int(limit)).offset(0)
        return query.options(
            joinedload(
                Product.extData
            ),
            joinedload(
                Product.brand

            ),
            joinedload(
                Product.category
            )).all()

    @classmethod
    def get_product_by_brand(cls,brand_id,product_id):
        query = Product.query.filter(Product.brand_id == brand_id).\
            filter(Product.id != product_id)
        query = query.options(
            joinedload(
                Product.extData
            ),
            joinedload(
                Product.brand

            ),
            joinedload(
                Product.category
            )
        )
        return query.all()

    @classmethod
    def get_category_by_id(cls, id):

        return ProductCategory.query.filter(ProductCategory.id == id).first()

    @classmethod
    def filter(cls, language=None, pagination=None,
               order_by='like_count', *args, **kwargs):

        query = Product.query \
            .filter(Product.status != Product.STATUE_REMOVED) \
            .order_by(Product.like_count)

        brand_id = kwargs.pop('brand_id',None)
        if brand_id:
            query = query.filter(Product.brand_id == brand_id)

        category_id = kwargs.pop('category_id', None)
        if category_id:
            query = query.filter(Product.category_id == category_id)
        brand_id = kwargs.get('brand_id', None)

        if brand_id:
            query = query.filter(Product.brand_id == brand_id)
        if 'keyword' in kwargs:
            query = query.filter(Product.title.like('%{}%'.format(kwargs.pop('keyword'))))

        product_id = kwargs.pop('product_id',None)
        if product_id:
            query = query.filter(Product.id == product_id)

        # 不需要type , 传category_id 上来
        # if 'type' in kwargs:
        #     category =ProductCategory.query.filter(ProductCategory.screen_name == kwargs.pop('type')).first()
        #     # query = query.filter(Product.category_id == category)
        #     query = query.filter(Product.category_id == category.id)
        min_price = kwargs.pop('min_price')
        max_price = kwargs.pop('max_price')

        if min_price:
            query = query.filter(Product.average_price >= min_price)

        if max_price:
            query = query.filter(Product.average_price <= max_price)

        ## 处理metadata  过淲
        metadata = kwargs.pop('metadata', None)
        if metadata and category_id and len(metadata.items()) > 0:
            need_join = False
            metadata_list = Metadata.query.filter(Metadata.type == category_id).all()
            metadata_dict = {}
            # 将列表转成 key-value 字典模式，方便查询
            for m in metadata_list:
                metadata_dict.setdefault(m.id, m)

            for key, value in metadata.items():
                metadata_def = metadata_dict.get(key, None)
                if not metadata_def:  continue  # TODO medatadata 定义不存在，是否报错？？？
                if isinstance(value, (tuple, list)):
                    # value 为元组或list 需要拆分值
                    if len(value) > 0:
                        query = _process_list_metadata_value(query, metadata_def, value)
                        need_join = True
                elif isinstance(value, dict):
                    if _check_dice_metadata_value(value):
                        query = _process_dict_metadata_value(query, metadata_def, value)
                        need_join = True
                else:
                    if value:
                        query = _process_value_metadata(query, metadata_def, str(value))
                        need_join = True
            if need_join:
                query = query.join(
                    ProductMetadata,
                    ProductMetadata.product_id == Product.id
                )


        query = query.options(
            joinedload(
                Product.category
            ),
            joinedload(
                Product.extData
            ),
            joinedload(
                Product.brand
            ),
            joinedload_all(
                Product.contents)

        )
        # query = query.limit(10).offset(0)
        print('最终SQL: ', query)

        if language:
            query = query.filter(ProductContent.language_code == 'zh_CN')

        if pagination:
            page_size = pagination.get('page_size', 10)
            page = pagination.get('current_page', 1)

            pagination = query.paginate(page, page_size, False)

            return pagination.items, pagination

        return query.all(), None
        # for i in l:
        #     print(i.contents)

    @classmethod
    def get_productmetadata_by_id(cls, id):
        return ProductMetadata.query.filter(ProductMetadata.product_id == id)

    @classmethod
    def get_product_gallery_by_id(cls,product_id):
        return ProductGallery.query.filter(ProductGallery.product_id == product_id).first()